# coding:utf-8

import os
import set_env_path

from sqlalchemy import create_engine
from sqlalchemy.sql import text
from sqlalchemy.orm import sessionmaker

engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
Session = sessionmaker(bind=engine)
session = Session()


def parse():
    """
    created by chenhj at 2018/4/28

    """
    init_sql = text("""
        delete from partic where deleted_at != 0
    """)

    sql = text("""
        select supplier_id, demand_id, published_count, count(*) from partic
         where deleted_at=0 group by supplier_id, demand_id, published_count having count(*) > 1;
    """)

    sql2 = text("""
        select id, status from partic
         where supplier_id=:supplier_id
          and demand_id=:demand_id 
          and published_count=:published_count
        order by updated_at
    """)

    del_sql = text("""
        delete from partic where id in :id
    """)

    session.execute(init_sql)

    res = session.execute(sql).fetchall()
    for each in res:
        print('*' * 30)
        print(each.supplier_id)
        print(each.demand_id)
        print(each.published_count)
        partic_list = session.execute(
            sql2,
            {
                'supplier_id': each.supplier_id,
                'demand_id': each.demand_id,
                'published_count': each.published_count
            }
        ).fetchall()
        deleted_ids = [p.id for p in partic_list]

        filter_id = 0
        filter_status = 0
        for p in partic_list:
            print(p.id)
            print(p.status)
            if not filter_id:
                filter_id = p.id
                filter_status = p.status
                continue

            if p.status == 200:
                filter_id = p.id
                filter_status = p.status

            if filter_status != 200 and p.status == 130:
                filter_id = p.id
                filter_status = p.status

            if filter_status == 10 and p.status == 10:
                filter_id = p.id
                filter_status = p.status
        deleted_ids.remove(filter_id)
        print(deleted_ids)
        session.execute(del_sql, {'id': tuple(deleted_ids)})
        session.commit()
        print('*' * 30)
    session.close()


if __name__ == '__main__':
    parse()